<?php

class BaseModel extends Model {

	public $table_record_count;
	protected $db_table = '';
	
	public function BaseModel() {
		parent::Model();
		$this->load->database();
	}

	public function setModel($model) {
		$this->db_table = $model;
	}
	
	//retrieves items that have been assigned to be published 'today' only
	public function getPublished($start = null, $count = null, $orderby = null) {
		$sql = "SELECT T.*
				FROM ".$this->db_table." T 
				WHERE T.active = 1 AND T.deleted = 0
					AND (T.publishon <= NOW() OR T.publishon IS NULL OR T.publishon = '0000-00-00 00:00:00')
					AND (T.publishoff >= NOW() OR T.publishoff IS NULL OR T.publishoff = '0000-00-00 00:00:00') ";
		
		//generate order by clause from string / array			
		$order_clause = '';
		if ($orderby) {
			if ( is_string($orderby) ) {
				$order_clause = " ORDER BY " . $orderby;
			} elseif ( is_array($orderby) ) {
				if ( count($orderby) > 0 ) {
					foreach ($orderby as $field => $value) {
						$orderby_list[] = " ".$field." " .$this->db->escape($value) . " ";
					}
					$order_clause = " ORDER BY " . join(' , ', $orderby_list );
				}
			}
		}
		
		//generate limit clause from start number and count
		$limit_clause = '';
		if ($count) {
			if ($count && $start) {
				$limit_clause = " LIMIT $start, $count ";
			}
			else {
				$limit_clause = " LIMIT $count ";
			}
		}
		
		//create full query and retreive result
		$sql .= $order_clause . $limit_clause;
		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs;
		else return false;
	}
	
	//flexible method for finding data items dependent on filters passed in as key->value pairs in $filters array
	public function find($filters = null, $start = null, $count = null, $orderby = null) {
		
		//generate where clause for query from filters array or string
		$where_clause = '';
		if ($filters) {
			if ( is_string($filters) ) $where_clause = $filters;
			elseif ( is_array($filters) ) {
				if ( count($filters) > 0 ) {
					foreach ($filters as $field => $value) {
						if (is_array($value)) { //use IN clause to limit results
							if (count($value) > 0) $filter_list[] = " T.".$field." IN (".implode(',', $value).") ";
							else $filter_list[] = " 0 = 1 ";
						} else if (substr($field, 0, 3) == 'md5') { //md5 modifier to find md5'd value
							$fieldparts = explode('|', $field);
							$filter_list[] = " md5(T.".$fieldparts[1].") = ".$this->db->escape($value)." ";
						} else if (substr($value, 0, 1) == '>') { //greater than modifier
							$valueparts = explode('>', $value);
							$filter_list[] = " T.".$field." > ".$this->db->escape($valueparts[1])." ";
						} else if (substr($value, 0, 1) == '<') { //less than modifier
							$valueparts = explode('<', $value);
							$filter_list[] = " T.".$field." < ".$this->db->escape($valueparts[1])." ";
						} else if (substr($value, 0, 1) == '!') { //not equal to modifier
							$valueparts = explode('!', $value);
							$filter_list[] = " T.".$field." != ".$this->db->escape($valueparts[1])." ";
						} else	if (substr($field, 0, 5) == 'MONTH') {
							$fieldparts = explode('|', $field);
							$filter_list[] = " MONTH(T.".$fieldparts[1].") = '$value' ";
						} else if (substr($field, 0, 4) == 'YEAR') {
							$fieldparts = explode('|', $field);
							$filter_list[] = " YEAR(T.".$fieldparts[1].") = '$value' ";
						} else $filter_list[] = " T.".$field." = ".$this->db->escape($value)." ";
					}
					$where_clause = " WHERE " . join(' AND ', $filter_list );
				}
			}
		}
		
		//build the order clause for this query
		$order_clause = '';
		if ($orderby) {
			if ( is_string($orderby) ) {
				$order_clause = " ORDER BY " . $orderby;
			} elseif ( is_array($orderby) ) {
				if ( count($orderby) > 0 ) {
					foreach ($orderby as $field => $value) {
						$orderby_list[] = " ".$field." " .$this->db->escape($value) . " ";
					}
					$order_clause = " ORDER BY " . join(' , ', $orderby_list );
				}
			}
		}
		
		//build limit clause from start row and number of rows required
		$limit_clause = '';
		if ($count) {
			if ($count && $start) {
				$limit_clause = " LIMIT $start, $count ";
			}
			else {
				$limit_clause = " LIMIT $count ";
			}
		}

		// Build up the SQL query string and run the query
		$sql = "SELECT T.*
				FROM ".$this->db_table." T ".
				$where_clause . 
				$order_clause . 
				$limit_clause;

		//get the total number of rows that this query would have retrieved
		$this->table_record_count = $this->db->query("SELECT T.*
				FROM ".$this->db_table." T ".
				 $where_clause)->num_rows();

		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs;
		else return false;

	}
	
	//retrieve record by primary key (generally 'id')
	public function retrieve_by_pkey($idField) {
		$sql = "SELECT T.*, CONCAT_WS(' ', U.firstname, U.lastname) as creator, CONCAT_WS(' ', U2.firstname, U2.lastname) as updater
				FROM ".$this->db_table." T
						LEFT JOIN users U ON (T.creatorid = U.id)
						LEFT JOIN users U2 ON (T.updaterid = U2.id)
				WHERE T.id = ".intval($idField)."
				LIMIT 1";

		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs[0];
		else return false;
	}
	
	//retrieve record by and arbitrary field
	public function retrieve_by_field($field, $value) {
		$sql = "SELECT T.*
				FROM ".$this->db_table." T
				WHERE T.`".$field."` = ".$this->db->escape($value)."
				LIMIT 1";

		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs[0];
		else return false;
	}

	//add a new record using the $data array as key->value pairs
	public function add( $data ) {
		$sql = $this->db->insert_string($this->db_table, $data);

		$query = $this->db->query($sql);

		if ($query) return $this->db->insert_id();
		else return false;
	}
	
	//modify a record using the $data array as key->value pairs
	public function modify($keyvalue, $data) {
		$where = "id = ".intval($keyvalue);
		$sql = $this->db->update_string($this->db_table, $data, $where);

		$query = $this->db->query($sql);

		if ($query) return true;
		else return false;
	}
	
	//delete a record or set of records using the $data array as key->value pairs
	public function delete_where($data) {
		if ($this->db->delete($this->db_table, $data) ) return true;
		else return true;
	}

	//quick method for updating an individual field	
	public function updateField($dataid, $fieldname, $fieldvalue) {
		$sql = "UPDATE ". $this->db_table ." SET ".$fieldname." = ".$this->db->escape($fieldvalue)." WHERE id = ".intval($dataid);
		$rs = $this->db->query($sql);
	}
	
	public function delete_by_pkey($idField){
		$sql = "DELETE T
				FROM ".$this->db_table." T
				WHERE T.id = ".intval($idField);

		$query = $this->db->query($sql);

		return true;
	}
	
	public function pseudoDelete($idField) {
		return $this->modify($idField, array('deleted' => 1, 'dateupdated' => date('Y-m-d H:m:s') ));
	}
	
	public function pseudoUndelete($idField) {
		return $this->modify($idField, array('deleted' => 0, 'dateupdated' => date('Y-m-d H:m:s') ));
	}
}

?>